CREATE PROCEDURE [dbo].[asi_GetCommunityRoster] (
      @communityDocumentKey uniqueidentifier
)
AS
BEGIN
    -- Get ParentHierarchyKey
    DECLARE @parentHierarchyKey uniqueidentifier
    SELECT @parentHierarchyKey = h.[ParentHierarchyKey]
      FROM [dbo].[DocumentMain] d
           INNER JOIN [dbo].[Hierarchy] h ON d.[DocumentVersionKey] = h.[UniformKey]
     WHERE d.[DocumentVersionKey] = @communityDocumentKey
           AND d.DocumentTypeCode = 'CTY'
           AND d.DocumentStatusCode = 40

    -- Get the list of all community documents underneath the parent of the specified community document
    DECLARE @communityDocument table
    (
        [DocumentVersionKey] uniqueidentifier PRIMARY KEY,
        [Title] nvarchar(100),
        [CreatedOn] datetime,
        [CreatedByUserKey] uniqueidentifier
    )

    INSERT INTO @communityDocument
        SELECT [DocumentVersionKey], [Title], [CreatedOn], [CreatedByUserKey]
          FROM [dbo].[asi_PublishedCommunitiesByDocumentKey](@communityDocumentKey)
         WHERE [DocumentTypeCode] = 'CTY'

    -- Get all subscribers to all groups for all documents
    DECLARE @subscriber table
    (
       [ContactKey] uniqueidentifier,
        [JoinDate] datetime
    )
             
    DECLARE @groupTypeKey uniqueidentifier
    DECLARE @subscriberRoleKey uniqueidentifier
    DECLARE @adminRoleKey uniqueidentifier
    DECLARE @wikiAuthorRoleKey uniqueidentifier
    DECLARE @communityModeratorRoleKey uniqueidentifier

    SET @groupTypeKey = '44B62E6D-CB59-4113-B3B9-D85E52F176BF'
    SET @subscriberRoleKey = '5BF5FE7A-BF40-4072-B7E7-FD250635BF95'
    SET @adminRoleKey = '13FE69F0-19B3-4F47-805C-64FF8E836469'
    SET @wikiAuthorRoleKey = '62FF03ED-8C63-4CA3-A83B-8E6349FC15B2'
    SET @communityModeratorRoleKey = '4789BA6A-674A-4917-A02A-398236F95D14'
    
    INSERT INTO @subscriber
        SELECT [MemberContactKey], MIN(m.[JoinDate])
          FROM @communityDocument cd
               INNER JOIN [dbo].[GroupMain] g ON g.[GroupTypeKey] = @groupTypeKey AND g.[Name] = LOWER(CAST(cd.[DocumentVersionKey] AS nvarchar(36)))
               INNER JOIN [dbo].[GroupMember] m ON g.[GroupKey] = m.[GroupKey] AND m.[IsActive] = 1
               INNER JOIN [dbo].[GroupMemberDetail] d ON m.[GroupMemberKey] = d.[GroupMemberKey]
                                                  AND d.[GroupRoleKey] IN (@subscriberRoleKey, @adminRoleKey, @wikiAuthorRoleKey, @communityModeratorRoleKey)
                                                  AND d.[IsActive] = 1
         GROUP BY [MemberContactKey]

    -- Return each subscriber's roster info
    SELECT s.[ContactKey], i.[FirstName], i.[LastName], c.[FullName], i.[PrimaryInstituteName], ii.[InstituteName], s.[JoinDate], [Title] AS [MostRecentPostTitle], [DocumentVersionKey] AS [MostRecentPostKey], d.[CreatedOn] AS [MostRecentPostOn]
      FROM @subscriber s
           INNER JOIN [dbo].[ContactMain] c ON s.[ContactKey] = c.[ContactKey]
           LEFT OUTER JOIN [dbo].[Individual] i ON s.[ContactKey] = i.[ContactKey]
           LEFT OUTER JOIN [dbo].[Institute] ii ON s.[ContactKey] = ii.[ContactKey]
           LEFT OUTER JOIN @communityDocument d ON s.[ContactKey] = d.[CreatedByUserKey]
     WHERE [DocumentVersionKey] IS NULL
           OR [DocumentVersionKey] = (SELECT TOP 1 [DocumentVersionKey]
                                        FROM @communityDocument cd
                                       WHERE cd.[CreatedByUserKey] = d.[CreatedByUserKey]
                                       ORDER BY cd.[CreatedOn] DESC)
END





GO